﻿/*
 * Created by SharpDevelop.
 * User: Михаил
 * Date: 08.12.2014
 * Time: 20:17
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */
using System;
using System.CodeDom;
using System.Data;
using System.Data.Linq;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using Region.Utils;
using System.Collections.Generic;
namespace Invent
{
	/// <summary>
	/// Description of Database.
	/// </summary>
	public class DatabaseInvent: DataSet, IDisposable 
	{
	  DirectoryInfo dbdir;
	  public DirectoryInfo Dir{get{return dbdir;}}
		public DataTable Devices; 
		public DataTable Delivery; // Поставка
		public DataTable Places; // Место
		public DataTable Employee; // Сотрудник
		public DataTable Foto; // Поставка
		String m_fileName = null;
		public DataRow newPlace(String PlaceName,Guid ParentPlaceUID)
		{
			DataRow row = Places.Rows.Add(Guid.NewGuid());
			row["ParentPlaceUID"] = ParentPlaceUID;
			row["PlaceName"] = PlaceName;
			return row;
		}
		public bool deletePlace(Guid PlaceUID)
		{
		  var qParent = from row in Places.AsEnumerable() 
		    where (Guid)row["ParentPlaceUID"]==PlaceUID
		          select row;
		  if (qParent.Any())
		    return false;
		  var qDevices = from row in Devices.AsEnumerable() 
		    where (Guid)row["PlaceUID"]==PlaceUID
		          select row;
		  if (qDevices.Any()) 
		    return false;	
		  DataRow DelRow = (
		    from row in Places.AsEnumerable()
		    where (Guid)row["PlaceUID"]==PlaceUID
		    select row).Single();
		  Places.Rows.Remove(DelRow);
		  return true;
		  
		}
		public DataRow newDevice(String InventNum,String DeviceName,Guid PlaceUID,Guid DeliveryUID,Guid EmployeeUID,String DeviceType,String DeviceModel)
		{
			DataRow row = Devices.Rows.Add(Guid.NewGuid());
			row["PlaceUID"] = PlaceUID;
			row["DeliveryUID"] = DeliveryUID;
			row["EmployeeUID"] = EmployeeUID;
			row["InventNum"] = InventNum;
			row["DeviceName"] = DeviceName;
			row["DeviceType"] = DeviceType;
			row["DeviceModel"] = DeviceModel;
			return row;
		}
		public bool deleteDevice(Guid DeviceUID)
		{
		  DataRow DelRow = (
		    from row in Devices.AsEnumerable()
		    where (Guid)row["DeviceUID"]==DeviceUID
		    select row).Single();
		  Devices.Rows.Remove(DelRow);
		  return true;
		  
		}		
		public DataRow newEmployee(String EmployeeName)
		{
			DataRow row = Employee.Rows.Add(Guid.NewGuid());
			row["EmployeeName"] = EmployeeName;
			return row;
		}
		public bool deleteEmployee(Guid EmployeeUID)
		{
		  var qDevices = from row in Devices.AsEnumerable() 
		    where (Guid)row["EmployeeUID"]==EmployeeUID
		    select row;
		  if (qDevices.Any())
		    return false;	
		  DataRow DelRow = (
		    from row in Employee.AsEnumerable()
		    where (Guid)row["EmployeeUID"]==EmployeeUID
		    select row).Single();
		  Employee.Rows.Remove(DelRow);
		  return true;
		  
		}
		public DataRow newDelivery(String DeliveryName, String DeliveryOrganization)
		{
			DataRow row = Delivery.Rows.Add(Guid.NewGuid());
			row["DeliveryName"] = DeliveryName;
			row["DeliveryOrganization"] = DeliveryOrganization;
			return row;
		}
		public bool deleteDelivery(Guid DeliveryUID)
		{
		  var qDevices = from row in Devices.AsEnumerable() 
		    where (Guid)row["DeliveryUID"]==DeliveryUID
		    select row;
		  if (qDevices.Any())
		    return false;	
		  DataRow DelRow = (
		    from row in Delivery.AsEnumerable()
		    where (Guid)row["DeliveryUID"]==DeliveryUID
		    select row).Single();
		  Delivery.Rows.Remove(DelRow);
		  return true;
		  
		}
		public Boolean checkLink(String Link)
		{
		  var fi = new FileInfo(Link);
		  if (!fi.Exists) return false;
		  DirectoryInfo di = fi.Directory;
		  Boolean subDir=false;
		  for(DirectoryInfo pdi = di;pdi!=null;pdi=pdi.Parent)
		    if (pdi.FullName==dbdir.FullName)
		      subDir=true;
		  return subDir;    
		}
		public String subLink(String Link)
		{
		  if (!checkLink(Link)) return null;
		  var fi = new FileInfo(Link);
		  return fi.FullName.Substring(dbdir.FullName.Length+1);
		}
		public DataRow newFoto(Guid DeviceUID,Guid DeliveryUID,String Link,String FotoInfo)
		{
		  if (!checkLink(Link)) 
		    return null;// Неправильная ссылка
		  String l = subLink(Link);
		  //var l2 = (from f in Foto.AsEnumerable() where (String)f["FileLink"]==l select f);
		  if ((from f in Foto.AsEnumerable() where (String)f["FileLink"]==l select f).Any())
		      return null;//Такое фото есть  базе;
		  
		  DataRow row = Foto.Rows.Add(Guid.NewGuid());
		  //Link = Link.Substring();
		  row["FileLink"] = l;
			row["FotoInfo"] = FotoInfo;
      row["DeviceUID"] = DeviceUID;			
      row["DeliveryUID"] = DeliveryUID;			
			return row;
		}
		public bool deleteFoto(Guid FotoUID)
		{
		  DataRow DelRow = (
		    from row in Foto.AsEnumerable()
		    where (Guid)row["FotoUID"]==FotoUID
		    select row).Single();
		  Foto.Rows.Remove(DelRow);
		  return true;		  
		}
		public DatabaseInvent(): base("Invent")
		{
			createDeviceTable();
			createPlaceTable();
			createEmployeeTable();
			createDeliveryTable();
			createFoto();
		}
		void createDeviceTable()
		{
			Devices = this.Tables.Add("Devices");
			var DeviceUID = Devices.Columns.Add("DeviceUID",typeof(Guid));
			
			DeviceUID.Unique = true;
			DeviceUID.DefaultValue = Guid.Empty;
			DeviceUID.AllowDBNull = false;
			var DeviceName = Devices.Columns.Add("DeviceName",typeof(String));
			DeviceName.Caption = "Наименование";
			DeviceName.DefaultValue = "";
			DeviceName.AllowDBNull = false;
			var DeviceModel = Devices.Columns.Add("DeviceModel",typeof(String));
			DeviceModel.Caption = "Модель";
			DeviceModel.DefaultValue = "";
			DeviceModel.AllowDBNull = false;

			var DeviceType = Devices.Columns.Add("DeviceType",typeof(String));
			DeviceType.Caption = "Тип";
			DeviceType.DefaultValue = "";
			DeviceType.AllowDBNull = false;
			var InventNum = Devices.Columns.Add("InventNum",typeof(String));
			InventNum.Caption = "Инв. номер";
			InventNum.DefaultValue = "";
			InventNum.AllowDBNull = false;
      		var SerialNum = Devices.Columns.Add("SerialNum",typeof(String));
			SerialNum.Caption = "Серийный номер";
			SerialNum.DefaultValue = "";
			SerialNum.AllowDBNull = false;			
			var DeliveryUID = Devices.Columns.Add("DeliveryUID",typeof(Guid));
			DeliveryUID.Caption = "Поставка";
			DeliveryUID.DefaultValue = Guid.Empty;
			DeliveryUID.AllowDBNull = false;
			
			var EmployeeUID = Devices.Columns.Add("EmployeeUID",typeof(Guid));
			EmployeeUID.Unique = false;
			EmployeeUID.DefaultValue = Guid.Empty;
			EmployeeUID.AllowDBNull = false;			
			
			var PlaceUID = Devices.Columns.Add("PlaceUID",typeof(Guid));
			PlaceUID.Unique = false;
			PlaceUID.DefaultValue = Guid.Empty;
			PlaceUID.AllowDBNull = false;			
			Devices.PrimaryKey = new DataColumn[]{DeviceUID};
		}
		void createPlaceTable()
		{
			Places = this.Tables.Add("Places");
			var PlaceUID = Places.Columns.Add("PlaceUID",typeof(Guid));
			PlaceUID.Unique = true;
			PlaceUID.DefaultValue = Guid.Empty;
			PlaceUID.AllowDBNull = false;
			var ParentPlaceUID = Places.Columns.Add("ParentPlaceUID",typeof(Guid));
			ParentPlaceUID.DefaultValue = Guid.Empty;
			ParentPlaceUID.AllowDBNull = false;
			var PlaceName = Places.Columns.Add("PlaceName",typeof(String));
			PlaceName.Caption = "Наименование";
			PlaceName.DefaultValue = "";
			PlaceName.AllowDBNull = false;
      Places.PrimaryKey = new DataColumn[]{PlaceUID};
		}
		void createEmployeeTable()
		{
			Employee = this.Tables.Add("Employee");
			var EmployeeUID = Employee.Columns.Add("EmployeeUID",typeof(Guid));
			EmployeeUID.Unique = true;
			EmployeeUID.DefaultValue = Guid.Empty;
			EmployeeUID.AllowDBNull = false;
			var EmployeeName = Employee.Columns.Add("EmployeeName",typeof(String));
			EmployeeName.Caption = "Имя";
			EmployeeName.DefaultValue = "";
			EmployeeName.AllowDBNull = false;
			Employee.PrimaryKey = new DataColumn[]{EmployeeUID};
		}
		void createDeliveryTable()
		{
			Delivery = this.Tables.Add("Delivery");
			var DeliveryUID = Delivery.Columns.Add("DeliveryUID",typeof(Guid));
			DeliveryUID.Unique = true;
			DeliveryUID.DefaultValue = Guid.Empty;
			DeliveryUID.AllowDBNull = false;
			var DeliveryName = Delivery.Columns.Add("DeliveryName",typeof(String));
			DeliveryName.Caption = "Имя";
			DeliveryName.DefaultValue = "";
			DeliveryName.AllowDBNull = false;
			var DeliveryOrganization = Delivery.Columns.Add("DeliveryOrganization",typeof(String));
			DeliveryOrganization.Caption = "Поставщик";
			DeliveryOrganization.DefaultValue = "";
			DeliveryOrganization.AllowDBNull = false;
			var DeliveryDate = Delivery.Columns.Add("DeliveryDate",typeof(DateTime));
			DeliveryDate.Caption = "Дата поставки";
			DeliveryDate.DefaultValue = null;
			DeliveryDate.AllowDBNull = true;
			Delivery.PrimaryKey = new DataColumn[]{DeliveryUID};
		}
		void createFoto()
		{
			Foto = this.Tables.Add("Foto");
			var FotoUID = Foto.Columns.Add("FotoUID",typeof(Guid));
			FotoUID.Unique = true;
			FotoUID.DefaultValue = Guid.Empty;
			FotoUID.AllowDBNull = false;
			var FotoInfo = Foto.Columns.Add("FotoInfo",typeof(String));
			FotoInfo.Caption = "Примечание";
			FotoInfo.DefaultValue = "";
			FotoInfo.AllowDBNull = false;
			var FileLink = Foto.Columns.Add("FileLink",typeof(String));
			FileLink.Caption = "ССылка на файл";
			FileLink.DefaultValue = "";
			FileLink.AllowDBNull = false;
			var DeviceUID = Foto.Columns.Add("DeviceUID",typeof(Guid));
			DeviceUID.Unique = false;
			DeviceUID.DefaultValue = Guid.Empty;
			DeviceUID.AllowDBNull = true;
			var DeliveryUID = Foto.Columns.Add("DeliveryUID",typeof(Guid));
			DeliveryUID.Unique = false;
			DeliveryUID.DefaultValue = Guid.Empty;
			DeliveryUID.AllowDBNull = true;			
					
			Foto.PrimaryKey = new DataColumn[]{FotoUID};

		}
		public void createStandardPlace()
		{
			var plUpr = newPlace("Управление",Guid.Empty);
			newPlace("Бухгалтерия",(Guid)plUpr["PlaceUID"]);
			newPlace("Кадры",(Guid)plUpr["PlaceUID"]);
			newPlace("СКП",(Guid)plUpr["PlaceUID"]);
		}

		public void control_read(Control root,DataRow row)
		{
			foreach(Control c in root.Controls)
			{
				var textBox = c as System.Windows.Forms.TextBox;
				var comboBox = c as System.Windows.Forms.ComboBox;
				var dateTimePicker = c as System.Windows.Forms.DateTimePicker;
				if (row.Table.Columns.Contains(c.Name))
				{
					DataColumn dc = row.Table.Columns[c.Name];
					if(textBox != null)
					{
						switch(dc.DataType.FullName)
						{
						    case "System.String": textBox.Text = (String)row[dc];break;
						default: throw new Exception("read textBox "+dc.DataType.FullName);
						}
					}
					else if(comboBox != null)
					{
						Object value = null;
						switch(dc.DataType.FullName)
						{
						    case "System.String": value = (String)row[dc];break;
						    case "System.Int32": value = (Int32)row[dc];break;
						    case "System.Guid": value = (Guid)row[dc];break;
						default: throw new Exception("read comboBox "+dc.DataType.FullName);
						
						
						}
						if (comboBox.DropDownStyle == ComboBoxStyle.DropDownList)
						{
							foreach(var i in comboBox.Items)
								if (i is ListItemValue && value.Equals(((ListItemValue)i).Value))
							{									
								comboBox.SelectedItem = i;
								break;
							}
						}
						//Если выпадающий редактируемый список
						else if (dc.DataType.FullName =="System.String")
							comboBox.Text = (String)value;
					}
					else if(dateTimePicker != null)
					{
						dateTimePicker.ShowCheckBox = true;
						switch(dc.DataType.FullName)
						{
						case "System.DateTime":
								if(row.IsNull(dc))
									dateTimePicker.Checked = false;
								else
								{
									dateTimePicker.Checked = true;
									dateTimePicker.Value = (DateTime)row[dc];
								}
							break;
						default: throw new Exception("read dateTimePicker "+dc.DataType.FullName);
						}
					}
				}
				else
					control_read(c, row);
			}
		}
		public void control_write(Control root,DataRow row)
		{
			foreach(Control c in root.Controls)
			{
				var textBox = c as System.Windows.Forms.TextBox;
				var comboBox = c as System.Windows.Forms.ComboBox;
				var dateTimePicker = c as System.Windows.Forms.DateTimePicker;
				if (row.Table.Columns.Contains(c.Name))
				{
					DataColumn dc = row.Table.Columns[c.Name];
					if(textBox != null)
					{
						switch(dc.DataType.FullName)
						{
						    case "System.String":row[dc] = (String)textBox.Text;break;
						default: throw new Exception(c.Name + " write textBox "+dc.DataType.FullName);
						}
					}
					else if(comboBox != null)
					{
						Object value = null;
						if (comboBox.DropDownStyle == ComboBoxStyle.DropDownList)
						{
							var si = comboBox.SelectedItem as ListItemValue;
							if (si!=null)
								value = si.Value;
							else
								value = DBNull.Value;
						}
						//Если выпадающий редактируемый список
						else if (dc.DataType.FullName =="System.String")
							value = comboBox.Text;
						
						switch(dc.DataType.FullName)
						{
						case "System.String": row.SetField<String>(dc,(String)value);break;
						case "System.Int32": row.SetField<Int32>(dc,(Int32)value);break;
						case "System.Guid": row.SetField<Guid>(dc,(Guid)value);break;
						default: throw new Exception(c.Name + " write comboBox "+dc.DataType.FullName);
						
						
						}

					}
					else if(dateTimePicker != null)
					{
						
						switch(dc.DataType.FullName)
						{
						case "System.DateTime":
							if (dateTimePicker.Checked)
								row.SetField<DateTime>(dc,dateTimePicker.Value);
							else
								row.SetField<DBNull>(dc,DBNull.Value);
							break;
						default: throw new Exception(c.Name + " write dateTimePicker "+dc.DataType.FullName);
						}
					}
				}
				else
					control_read(c, row);
			}
		}
		public void load(string FileName)
		{
			try
			{
			  var fi = new FileInfo(FileName);
			  m_fileName = FileName; 
			  if (fi.Directory.Exists) 
			    dbdir = fi.Directory;
			  this.ReadXml(m_fileName);
			}
			catch(FileNotFoundException){}// если такого файла нет проигнорировать ошибку
		}
		public void save(string FileName)
		{
			m_fileName = FileName;
			var fi = new FileInfo(FileName);
			if (fi.Directory.Exists)
			    dbdir = fi.Directory;
			this.WriteXml(m_fileName);
		}
		public void Dispose()
		{
			if (m_fileName!=null)
				save(m_fileName);
			base.Dispose();
		}
		public String getPlaceName(Guid PlaceUID)
		{
		  if (PlaceUID == Guid.Empty) return "";		    
		  foreach(var r in 
		          from row in Places.AsEnumerable() 
		          where row.Field<Guid>("PlaceUID")==PlaceUID 
		          select row)
		  {
		    String N = getPlaceName(r.Field<Guid>("ParentPlaceUID"));
		    return ((N!="")?N+":":"")+r.Field<String>("PlaceName");
		  }
		  return "";
		}
		public String getEmployeeName(Guid EmployeeUID)
		{
		  if (EmployeeUID == Guid.Empty) return "";				 
		  foreach(var r in 
		          from row in Employee.AsEnumerable() 
		          where row.Field<Guid>("EmployeeUID")==EmployeeUID 
		          select row)
		  {
        return r.Field<String>("EmployeeName");
		  }
		  return "";
		}
		public String getDeliveryName(Guid DeliveryUID)
		{
		  if (DeliveryUID == Guid.Empty) return "";				 
		  foreach(var r in 
		          from row in Delivery.AsEnumerable() 
		          where row.Field<Guid>("DeliveryUID")==DeliveryUID 
		          select row)
		  {
        return r.Field<String>("DeliveryName");
		  }
		  return "";
		}
		public bool ExportXLSX(String TemplateName, Dictionary<string, object> Poles, Dictionary<string, List<Dictionary<string, object>>> Tables = null)
    {
      var Doc = new ExportXLSX();
      using(var s = new FileStream(TemplateName,FileMode.Open))
      {
        s.Seek(0,SeekOrigin.End);
        int size = (Int32)s.Position;
        Doc.Template = new byte[size];
        s.Seek(0,SeekOrigin.Begin);
        s.Read(Doc.Template,0,size);
      }
      var sv = new SaveFileDialog();
      sv.DefaultExt = "xlsx";
      sv.Filter  = "Excel (*.xlsx)|*.xlsx";
      if (sv.ShowDialog()!=DialogResult.OK) return false;
       
      Doc.OutFile = sv.FileName;
      Doc.Poles = Poles;
      Doc.Tables = Tables;
      Doc.Save();
      // Открыть файл во внешней программе
      var pr = new System.Diagnostics.Process();
      pr.StartInfo.FileName = Doc.OutFile;
      pr.Start(); 
      return true;
    }
	}
}
